const Sequelize = require('sequelize')
const Op = Sequelize.Op;

const sequelize = require('../db');

const Model = sequelize.define('stat_behavior',
    {
        sub_type: { type: Sequelize.STRING, },
        url: { type: Sequelize.STRING },
        old_url: { type: Sequelize.STRING },
        remain_time: { type: Sequelize.INTEGER, },
        click_text: { type: Sequelize.STRING, },
        click_html: { type: Sequelize.STRING, },
        node_name: { type: Sequelize.STRING, },
        ip: { type: Sequelize.STRING, },
        ua: { type: Sequelize.STRING, },
    },
    {
        tableName: 'stat_behavior',
        // 不要忘记启用时间戳！
        timestamps: true,

        // 不想要 false
        createdAt: 'create_time',

        // 想要 updatedAt 但是希望名称叫做 updateTimestamp
        updatedAt: false
    }
);

module.exports.add = async (data) => await Model.create(data)
module.exports.addBatch = async (datas) => await Model.bulkCreate(datas)

module.exports.del = async (id) => await Model.destroy({ where: { id } })


module.exports.update = async (data) => {
    let newUser = { ...data }
    delete newUser['id']
    return await Model.update({ ...newUser }, {
        where: { id: data.id }
    })
}

module.exports.findOne = async (condition) => {
    let users = await Model.findAll({ where: condition })
    return users[0]
}

module.exports.findAll = async (condition = {}) => {
    return await Model.findAll({ where: condition })
}

module.exports.getIpList = async ({date}) => {
    return await Model.findAll({
        attributes: ['ip', [Sequelize.fn("COUNT", Sequelize.col('ip')), 'count']],
        order: [[Sequelize.col('count'), 'DESC']],
        where: {
            create_time: {
                [Op.gt]: date
            },
            sub_type: {
                [Op.in]: ['replaceState', 'pushState', 'popstate', 'pagevisit']
            }
        },
        group: 'ip',
    })
}

module.exports.getPvLimit = async ({date, ip, limit=10 }) => {
    let ipObj = ip ? { ip }: {}
    return await Model.findAll({
        attributes: ['url', [Sequelize.fn("COUNT", Sequelize.col('url')), 'count']],
        order: [[Sequelize.col('count'), 'DESC']],
        where: {
            create_time: {
                [Op.gt]: date
            },
            ...ipObj,
            sub_type: {
                [Op.in]: ['replaceState', 'pushState', 'popstate', 'pagevisit']
            }
        },
        group: 'url',
        limit
    })
}

module.exports.getIpVisitCount = async ({date }) => {
    return await Model.findAll({
        attributes: [ 'url', 'ip', [Sequelize.fn("COUNT", Sequelize.col('ip')), 'count']],
        order: [[Sequelize.col('count'), 'DESC']],
        where: {
            create_time: {
                [Op.gt]: date
            },
            sub_type: {
                [Op.in]: ['replaceState', 'pushState', 'popstate', 'pagevisit']
            }
        },
        group: ['url', 'ip'],
    })
}

module.exports.getRemainList = async ({limit=10, offset=0}) => {
    return await Model.findAndCountAll({
        limit,
        offset,
        order: [['create_time', 'DESC']],
        where: {
            sub_type: {
                [Op.in]: ['remain_replaceState', 'remain_pushState', 'remain_popstate', 'remain_beforeunload']
            }
        }
    })
}
module.exports.getClickList = async ({limit=10, offset=0}) => {
    return await Model.findAndCountAll({
        limit,
        offset,
        order: [['create_time', 'DESC']],
        where: {
            sub_type: {
                [Op.eq]: 'click'
            }
        }
    })
}

module.exports.getHourGroup = async ({date}) => {
    return await sequelize.query(
        `select url, DATE_FORMAT( create_time, '%H' ) AS hour, COUNT(1) as count
        from stat_behavior
        where create_time >= '${date+' 00:00:00'}' and create_time <= '${date+' 23:59:59'}'
        GROUP BY hour, url
        ORDER BY hour
         `,
        { type: Sequelize.QueryTypes.SELECT })
}